package com.lic.finance.excel;

import com.lic.finance.po.OutgoItem;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtils {
    public static final String TEMPLATE_FILE_PATH = "source_files/wacai365_outgo.xls";
    public static final String CREATION_FILE_PATH = "source_files/creation.xls";

    private Workbook mWorkbook;
    private WritableWorkbook mWritableWorkbook;

    private String[] categories;
    private String[] accounts;
    private String[] projects;
    private String[] writeOff;

    public void setTemplateFile(String templateFilePath) {
        File templateFile = new File(templateFilePath);
        if (templateFile.exists() && templateFile.isFile()) {
            try {
                mWorkbook = Workbook.getWorkbook(templateFile);
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("Do not find template file from path : " + templateFile.getPath());
        }
    }

    public void setCreationFile(String creationFilePath) {
        File creationFile = new File(creationFilePath);
        try {
            if (mWorkbook != null)
                mWritableWorkbook = Workbook.createWorkbook(creationFile, mWorkbook);
            else
                mWritableWorkbook = Workbook.createWorkbook(creationFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public String[] readEnumWacai() {
        // Read outgoTypes
        Sheet sheet = mWorkbook.getSheet(1);
        int readStartRow = 2;
        int readColumn = 1;
        categories = new String[sheet.getRows() - readStartRow];
        for (int i = readStartRow; i < sheet.getRows(); i++) {
            categories[i-readStartRow] = sheet.getCell(readColumn, i).getContents();
        }

        // Read accounts
        sheet = mWorkbook.getSheet(2);
        readColumn = 2;
        accounts = new String[sheet.getRows() - readStartRow];
        for (int i = readStartRow; i < sheet.getRows(); i++) {
            accounts[i-readStartRow] = sheet.getCell(readColumn, i).getContents();
        }

        // Read projects
        sheet = mWorkbook.getSheet(3);
        readColumn = 3;
        projects = new String[sheet.getRows() - readStartRow];
        for (int i = readStartRow; i < sheet.getRows(); i++) {
            projects[i-readStartRow] = sheet.getCell(readColumn, i).getContents();
        }

        //Read isWriteOff
        sheet = mWorkbook.getSheet(4);
        readColumn = 4;
        writeOff = new String[sheet.getRows() - readStartRow];
        for (int i = readStartRow; i < sheet.getRows(); i++) {
            writeOff[i-readStartRow] = sheet.getCell(readColumn, i).getContents();
        }

        return writeOff;
    }

    public String[] getCategories() {
        return categories;
    }

    public String[] getAccounts() {
        return accounts;
    }

    public String[] getProjects() {
        return projects;
    }

    public String[] getWriteOff() {
        return writeOff;
    }

    public CellFormat getCellFormat(int sheetIndex, int column, int row) {
        Cell cell = mWorkbook.getSheet(sheetIndex).getCell(column, row);
        return cell.getCellFormat();
    }

    public void writeToExcel(List<OutgoItem> list) {
        OutgoItem item = null;
        WritableSheet sheet = mWritableWorkbook.getSheet(0);
        WritableCellFormat dateTimeCellFormat = new WritableCellFormat(getCellFormat(0,5,2));
        Label label = new Label(0, 0, "^-^");
        int writeStartRow = 2;
        int writeStartColumn = 1;
        for(int i = 0; i < list.size(); i++) {
            item = list.get(i);
            try {
                label = new Label(writeStartColumn, writeStartRow, item.getCategory());
                sheet.addCell(label);
                label = new Label(++writeStartColumn, writeStartRow, item.getAccount());
                sheet.addCell(label);
                label = new Label(++writeStartColumn, writeStartRow, item.getProject());
                sheet.addCell(label);
                label = new Label(++writeStartColumn, writeStartRow, item.getWriteOff());
                sheet.addCell(label);
                DateTime dt = new DateTime(++writeStartColumn, writeStartRow, item.getDate(), dateTimeCellFormat);
                sheet.addCell(dt);
                Number number = new Number(++writeStartColumn, writeStartRow, item.getAmount());
                sheet.addCell(number);
                label = new Label(++writeStartColumn, writeStartRow, item.getDescription());
                sheet.addCell(label);
            } catch (RowsExceededException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
            writeStartRow++;
            writeStartColumn = 1;
        }
    }

    public void closeAll() {
        mWorkbook.close();
        try {
            mWritableWorkbook.write();
            mWritableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public Workbook getWorkbook() {
        return mWorkbook;
    }

    public static void main(String[] args) {
        ExcelUtils eu = new ExcelUtils();
        eu.setTemplateFile(TEMPLATE_FILE_PATH);
        eu.setCreationFile(CREATION_FILE_PATH);

        String[] o = eu.readEnumWacai();
        for(int i = 0; i < o.length; i++) {
            System.out.print(i + ": ");
            System.out.println(o[i]);
        }

        ArrayList<OutgoItem> list = new ArrayList<OutgoItem>();
        OutgoItem item = new OutgoItem();
        item.setAccount("现金");
        item.setDate(Calendar.getInstance().getTime());
        item.setWriteOff("是");
        item.setCategory("食物");
        item.setProject("日常");
        item.setDescription("Good work!");
        item.setAmount(15.0);
        list.add(item);

        eu.writeToExcel(list);
        eu.closeAll();
    }

}
